﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Zgq.CommonTools
{
    /// <summary>
    /// 数据库操作
    /// </summary>
    public class MSSqlParameterDB : IC5SqlDB
    {
        #region 新增
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="sqlparameters"></param>
        /// <returns></returns>
        public string GetInsertSql(object model, string sysDatabaseName = null, List<object> sqlparameters = null)
        {
            StringBuilder filedsSb = new StringBuilder();
            StringBuilder valuesSb = new StringBuilder();
            List<SqlParameter> tempParameters = new List<SqlParameter>();

            Type type = model.GetType();
            //获取类名
            String className = type.Name;
            //获取所有公有属性
            PropertyInfo[] info = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | System.Reflection.BindingFlags.DeclaredOnly);
            foreach (PropertyInfo prop in info)
            {
                var c5SqlModel = GetC5SqlModelAttribute(sysDatabaseName, className, prop);
                if (c5SqlModel == null)
                    continue;

                if (c5SqlModel.AutoIncrement) continue;
                if (c5SqlModel.IsTimeStamp) continue;

                filedsSb.AppendFormat("[{0}], ", prop.Name);
                valuesSb.AppendFormat("@{0}, ", prop.Name);

                object oVal = prop.GetValue(model, null);

                SqlParameter sqlParam = GetSqlParameter(c5SqlModel, prop.Name, oVal);
                tempParameters.Add(sqlParam);
            }
            if (sqlparameters != null) sqlparameters.AddRange(tempParameters);

            filedsSb.Remove(filedsSb.Length - 2, 2);
            valuesSb.Remove(valuesSb.Length - 2, 2);

            string sql = string.Format("INSERT INTO [{0}]({1}) values({2})", className, filedsSb, valuesSb);

            return sql;
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="sqlparameters"></param>
        /// <returns></returns>
        public string GetUpdateSql(object model, string sysDatabaseName = null, List<object> sqlparameters = null)
        {
            StringBuilder sqlStr = new StringBuilder();
            StringBuilder sqlWhereStr = new StringBuilder();
            List<SqlParameter> tempParameters = new List<SqlParameter>();

            Type type = model.GetType();
            //获取类名
            String className = type.Name;
            //获取所有公有属性
            PropertyInfo[] info = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | System.Reflection.BindingFlags.DeclaredOnly);
            foreach (PropertyInfo prop in info)
            {
                var c5SqlModel = GetC5SqlModelAttribute(sysDatabaseName, className, prop);
                if (c5SqlModel == null)
                    continue;

                if (c5SqlModel.IsTimeStamp) continue;

                object oVal = prop.GetValue(model, null);
                SqlParameter sqlParam = GetSqlParameter(c5SqlModel, prop.Name, oVal);
                tempParameters.Add(sqlParam);

                if (c5SqlModel.PrimaryKey)
                {
                    if (sqlWhereStr.Length > 0) sqlWhereStr.Append(" AND ");
                    sqlWhereStr.AppendFormat("[{0}] = @{0}", prop.Name);
                    continue;
                }

                if (c5SqlModel.IsNotModif || c5SqlModel.AutoIncrement) continue;

                sqlStr.AppendFormat("[{0}] = @{0}, ", prop.Name);
            }

            if (sqlparameters != null) sqlparameters.AddRange(tempParameters);

            sqlStr.Remove(sqlStr.Length - 2, 2);

            if (sqlWhereStr.Length == 0)
                throw new Exception("不存在主键，不能更新操作！");

            string sql = string.Format("UPDATE [{0}] SET {1} WHERE {2}", className, sqlStr, sqlWhereStr);
            return sql;
        }
        #endregion

        #region 批量删除
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="lists"></param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="sqlparameters"></param>
        /// <returns></returns>
        public string GetDeleteListSql(object[] lists, string sysDatabaseName = null, List<object> sqlparameters = null)
        {
            if (lists == null || lists.Count() == 0) return null;

            StringBuilder sqlWhereStr = new StringBuilder();
            List<SqlParameter> tempParameters = new List<SqlParameter>();

            object _firstT = lists[0];

            Type type = _firstT.GetType();
            //获取类名
            String className = type.Name;
            //主键集合
            string[] keys = type.GetPrimaryKeysByType();
            if (keys == null || keys.Count() == 0)
                throw new Exception("无法获取到当前对象的主键");

            StringBuilder strSingleTableWhere = new StringBuilder();
            for (int i = 0; i < lists.Length; i++)// item in lists)
            {
                var item = lists[i];

                if (item.GetType() != type)
                    throw new Exception("只支持同一个对象批量删除");

                strSingleTableWhere.Clear();
                foreach (var key in keys)
                {
                    var prop = item.GetType().GetProperty(key);
                    var c5SqlModel = GetC5SqlModelAttribute(sysDatabaseName, className, prop);
                    object oVal = prop.GetValue(item, null);

                    if (strSingleTableWhere.Length > 0) strSingleTableWhere.Append(" AND ");
                    strSingleTableWhere.AppendFormat("[{0}] = @{0}{1}", key, i);

                    SqlParameter sqlParam = GetSqlParameter(c5SqlModel, string.Format("{0}{1}", key, i), oVal);
                    tempParameters.Add(sqlParam);
                    continue;
                }

                sqlWhereStr.AppendFormat("({0}) OR ", strSingleTableWhere);
            }

            if (sqlWhereStr.Length < 8)
                throw new Exception("不存在主键，不能执行删除操作！");
            else
                sqlWhereStr.Remove(sqlWhereStr.Length - 4, 4);

            if (sqlparameters != null) sqlparameters.AddRange(tempParameters);

            string sql = string.Format("DELETE [{0}] WHERE {1}", className, sqlWhereStr);
            return sql;
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="model"></param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="sqlparameters"></param>
        /// <returns></returns>
        public string GetDeleteSql(object model, string sysDatabaseName = null, List<object> sqlparameters = null)
        {
            StringBuilder sqlWhereStr = new StringBuilder();
            List<SqlParameter> tempParameters = new List<SqlParameter>();

            Type type = model.GetType();
            //获取类名
            String className = type.Name;

            //主键集合
            string[] keys = type.GetPrimaryKeysByType();
            if (keys == null || keys.Count() == 0)
                throw new Exception("无法获取到当前对象的主键");

            foreach (var key in keys)
            {
                var prop = model.GetType().GetProperty(key);
                var c5SqlModel = GetC5SqlModelAttribute(sysDatabaseName, className, prop);
                object oVal = prop.GetValue(model, null);

                if (sqlWhereStr.Length > 0) sqlWhereStr.Append(" AND ");
                sqlWhereStr.AppendFormat("[{0}] = @{0}", key);

                SqlParameter sqlParam = GetSqlParameter(c5SqlModel, key, oVal);
                tempParameters.Add(sqlParam);
            }

            if (sqlWhereStr.Length == 0)
                throw new Exception("不存在主键，不能执行删除操作！");

            if (sqlparameters != null) sqlparameters.AddRange(tempParameters);

            String sql = String.Format("DELETE FROM [{0}] WHERE {1}", className, sqlWhereStr);

            return sql;
        }
        #endregion

        #region 查询数据是否存在
        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="model"></param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public string GetExistsSql(object model, string sysDatabaseName = null, string whereStr = null)
        {
            Type type = model.GetType();
            //获取类名
            String className = type.Name;
            StringBuilder sqlStr = new StringBuilder();
            if (C5DataBaseHelper.UseCache == CacheFlag.CacheView || C5DataBaseHelper.UseCache == CacheFlag.CacheAll)
            {
                var cachedata = SqlCache.GetSqlByCode(sysDatabaseName, className);
                if (cachedata != null && cachedata.CodeType == 2)
                {
                    sqlStr.AppendFormat(" SELECT count(1) FROM ({0}) as ResultView ", cachedata.SqlStr);
                }
                else
                {
                    sqlStr.AppendFormat(" SELECT count(1) FROM [{0}] ", className);
                }
            }
            else
            {
                sqlStr.AppendFormat("SELECT 1 FROM [{0}]", className);
            }
            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                if (!whereStr.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase))
                    sqlStr.Append(" WHERE ");
                sqlStr.AppendFormat(" {0}", whereStr);
            }
            return sqlStr.ToString();
        }
        #endregion

        #region 查询数据集
        /// <summary>
        /// 查询数据集
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <param name="sysDatabaseName"></param>
        /// <returns></returns>
        public string SelectList<T>(string sysDatabaseName = null, string whereStr = null, string orderByStr = null)
        {
            Type type = typeof(T);

            //获取类名
            String className = type.Name;

            StringBuilder sqlStr = new StringBuilder();
            if (C5DataBaseHelper.UseCache == CacheFlag.CacheView || C5DataBaseHelper.UseCache == CacheFlag.CacheAll)
            {
                var model = SqlCache.GetSqlByCode(sysDatabaseName, className);
                if (model != null && model.CodeType == 2)
                {
                    sqlStr.AppendFormat(" SELECT * FROM ({0}) as ResultView ", model.SqlStr);
                }
                else
                {
                    sqlStr.AppendFormat(" SELECT * FROM [{0}] ", className);
                }
            }
            else
            {
                sqlStr.AppendFormat(" SELECT * FROM [{0}] ", className);
            }

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                if (!whereStr.Trim().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                    sqlStr.Append(" WHERE ");
                sqlStr.AppendFormat(" {0}", whereStr);
            }

            if (!string.IsNullOrWhiteSpace(orderByStr))
            {
                if (!orderByStr.Trim().StartsWith("ORDER BY", StringComparison.CurrentCultureIgnoreCase))
                    orderByStr = string.Concat(" ORDER BY ", orderByStr);
                sqlStr.AppendFormat(" {0} ", orderByStr);
            }

            return sqlStr.ToString();
        }
        #endregion

        #region 查询分页数据集
        /// <summary>
        /// 查询分页数据集
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex">页索引(从0开始)</param>
        /// <param name="pageSize">页数量</param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public string SelectListPaging<T>(int pageIndex, int pageSize, string sysDatabaseName = null, string whereStr = null, string orderByStr = null)
        {
            int startIndex = pageIndex * pageSize + 1;

            int endIndex = (pageIndex + 1) * pageSize;

            Type type = typeof(T);

            //获取类名
            String className = type.Name;

            //默认以主键排序
            if (string.IsNullOrWhiteSpace(orderByStr))
            {
                //取出主键属性
                var parimaryKeyNameList = type.GetPrimaryKeysByType();
                if (parimaryKeyNameList == null || parimaryKeyNameList.Count() == 0)
                    throw new Exception(string.Format("实体【{0}】无主键，请检查！", className));

                orderByStr = string.Join(",", parimaryKeyNameList);
            }

            if (!orderByStr.Trim().StartsWith("ORDER BY", StringComparison.CurrentCultureIgnoreCase))
                orderByStr = string.Concat(" ORDER BY ", orderByStr);

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                if (!whereStr.Trim().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                    whereStr = string.Concat(" WHERE ", whereStr);
            }

            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append(" SELECT * FROM ( ");
            if (C5DataBaseHelper.UseCache == CacheFlag.CacheView || C5DataBaseHelper.UseCache == CacheFlag.CacheAll)
            {
                var model = SqlCache.GetSqlByCode(sysDatabaseName, className);
                if (model != null && model.CodeType == 2)
                {
                    sqlStr.AppendFormat(" SELECT ROW_NUMBER() OVER({0}) AS RowIndex,* FROM ({1}{2}) as ResultView ", orderByStr, model.SqlStr, whereStr ?? "");
                }
                else
                {
                    sqlStr.AppendFormat("   SELECT ROW_NUMBER() OVER({0}) AS RowIndex,* FROM [{1}] {2} ", orderByStr, className, whereStr ?? "");
                }
            }
            else
            {
                sqlStr.AppendFormat("   SELECT ROW_NUMBER() OVER({0}) AS RowIndex,* FROM [{1}] {2}", orderByStr, className, whereStr ?? "");
            }
            sqlStr.Append(" ) AS TempTable ");

            sqlStr.AppendFormat(" WHERE RowIndex >= {0} AND RowIndex <= {1} ", startIndex, endIndex);
            sqlStr.AppendFormat(" {0} ", orderByStr);

            return sqlStr.ToString();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <param name="colums"></param>
        /// <returns></returns>
        public string SelectListPagingColumn<T>(int pageIndex, int pageSize, string sysDatabaseName = null, string whereStr = null, string orderByStr = null, params string[] colums)
        {
            int startIndex = pageIndex * pageSize + 1;

            int endIndex = (pageIndex + 1) * pageSize;

            Type type = typeof(T);

            //获取类名
            String className = type.Name;

            //默认以主键排序
            if (string.IsNullOrWhiteSpace(orderByStr))
            {
                //取出主键属性
                var parimaryKeyNameList = type.GetPrimaryKeysByType();
                if (parimaryKeyNameList == null || parimaryKeyNameList.Count() == 0)
                    throw new Exception(string.Format("实体【{0}】无主键，请检查！", className));

                orderByStr = string.Join(",", parimaryKeyNameList);
            }

            if (!orderByStr.Trim().StartsWith("ORDER BY", StringComparison.CurrentCultureIgnoreCase))
                orderByStr = string.Concat(" ORDER BY ", orderByStr);

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                if (!whereStr.Trim().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                    whereStr = string.Concat(" WHERE ", whereStr);
            }

            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append(" SELECT * FROM ( ");
            if (C5DataBaseHelper.UseCache == CacheFlag.CacheView || C5DataBaseHelper.UseCache == CacheFlag.CacheAll)
            {
                var model = SqlCache.GetSqlByCode(sysDatabaseName, className);
                if (model != null && model.CodeType == 2)
                {
                    sqlStr.AppendFormat(" SELECT ROW_NUMBER() OVER({0}) AS RowIndex,{1} FROM ({2}{3}) as ResultView ", orderByStr, string.Join(",", colums), model.SqlStr, whereStr ?? "");
                }
                else
                {
                    sqlStr.AppendFormat(" SELECT ROW_NUMBER() OVER({0}) AS RowIndex,{1} FROM [{2}] {3} ", orderByStr, string.Join(",", colums), className, whereStr ?? "");
                }
            }
            else
            {
                sqlStr.AppendFormat("   SELECT ROW_NUMBER() OVER({0}) AS RowIndex,{1} FROM [{2}] {3}", orderByStr, string.Join(",", colums), className, whereStr ?? "");
            }
            sqlStr.Append(" ) AS TempTable ");

            sqlStr.AppendFormat(" WHERE RowIndex >= {0} AND RowIndex <= {1} ", startIndex, endIndex);
            sqlStr.AppendFormat(" {0} ", orderByStr);

            return sqlStr.ToString();
        }

        #endregion

        #region 查询实体
        /// <summary>
        /// 查询实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public string SelectModel<T>(string sysDatabaseName = null, string whereStr = null, string orderByStr = null)
        {
            Type type = typeof(T);

            //获取类名
            String className = type.Name;

            StringBuilder sqlStr = new StringBuilder();
            if (C5DataBaseHelper.UseCache == CacheFlag.CacheView || C5DataBaseHelper.UseCache == CacheFlag.CacheAll)
            {
                var model = SqlCache.GetSqlByCode(sysDatabaseName, className);
                if (model != null && model.CodeType == 2)
                {
                    sqlStr.AppendFormat("SELECT * FROM ({0}) as ResultView", className);
                }
                else
                {
                    sqlStr.AppendFormat("SELECT * FROM [{0}]", className);
                }
            }
            else
            {
                sqlStr.AppendFormat("SELECT * FROM [{0}]", className);
            }

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                if (!whereStr.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase))
                    sqlStr.Append(" WHERE ");
                sqlStr.AppendFormat(" {0}", whereStr);
            }
            if (!string.IsNullOrEmpty(orderByStr))
            {
                sqlStr.AppendFormat(" ORDER BY {0}", orderByStr);

            }

            return sqlStr.ToString();
        }
        #endregion

        #region 查询数据总量

        /// <summary>
        /// 查询数据数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public string SelectModelCount<T>(string sysDatabaseName = null, string whereStr = null)
        {
            Type type = typeof(T);

            String className = type.Name;

            StringBuilder sqlStr = new StringBuilder();
            if (C5DataBaseHelper.UseCache == CacheFlag.CacheView || C5DataBaseHelper.UseCache == CacheFlag.CacheAll)
            {
                var model = SqlCache.GetSqlByCode(sysDatabaseName, className);
                if (model == null)
                    throw new Exception("找不到表或者视图信息");

                if (model.CodeType == 1 || model.CodeType == 2)
                {
                    sqlStr.AppendFormat("SELECT count(1) FROM ({0}) as ResultView", model.SqlStr);
                }
                else
                {
                    sqlStr.AppendFormat("SELECT count(1) FROM [{0}]", className);
                }
            }
            else
            {
                sqlStr.AppendFormat("SELECT count(1) FROM [{0}]", className);
            }

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                if (!whereStr.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase))
                    sqlStr.Append(" WHERE ");
                sqlStr.AppendFormat(" {0}", whereStr);
            }

            return sqlStr.ToString();
        }

        #endregion

        #region 查询Datatable

        /// <summary>
        /// 查询Datatable
        /// </summary>
        /// <param name="tableCode"></param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <param name="colums"></param>
        /// <returns></returns>
        public string SelectDatatable(string tableCode, string sysDatabaseName = null, string whereStr = null, string orderByStr = null, params string[] colums)
        {
            if (string.IsNullOrWhiteSpace(tableCode))
                throw new Exception("表名或视图名称不存在");

            StringBuilder sqlStr = new StringBuilder();
            bool isViewCode = false;
            if (C5DataBaseHelper.UseCache == CacheFlag.CacheView || C5DataBaseHelper.UseCache == CacheFlag.CacheAll)
            {
                var model = SqlCache.GetSqlByCode(sysDatabaseName, tableCode);
                if (model != null && model.CodeType == 2)
                {
                    tableCode = model.SqlStr;
                    isViewCode = true;
                }
            }

            string colStr = colums == null || colums.Length == 0 ? "*" : string.Join(",", colums);
            sqlStr.AppendFormat(" select {0} from {1} ", colStr, isViewCode ? string.Format("({0}) as ResultView", tableCode) : tableCode);

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                if (!whereStr.Trim().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                    sqlStr.Append(" WHERE ");
                sqlStr.AppendFormat(" {0}", whereStr);
            }

            if (!string.IsNullOrWhiteSpace(orderByStr))
            {
                if (!orderByStr.Trim().StartsWith("ORDER BY", StringComparison.CurrentCultureIgnoreCase))
                    orderByStr = string.Concat(" ORDER BY ", orderByStr);
                sqlStr.AppendFormat(" {0} ", orderByStr);
            }

            return sqlStr.ToString();
        }

        #endregion

        #region 查询Datatable启用分页

        /// <summary>
        /// 查询Datatable启用分页
        /// </summary>
        /// <param name="tableCode"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <param name="colums"></param>
        /// <returns></returns>
        public string SelectDatatablePaging(string tableCode, int pageIndex, int pageSize, string sysDatabaseName = null, string whereStr = null, string orderByStr = null, params string[] colums)
        {
            if (string.IsNullOrWhiteSpace(tableCode))
                throw new Exception("表名或视图名称不存在");

            if (string.IsNullOrWhiteSpace(orderByStr))
                throw new Exception("分页必须要配置排序条件");

            StringBuilder sqlStr = new StringBuilder();
            bool isViewCode = false;
            if (C5DataBaseHelper.UseCache == CacheFlag.CacheView || C5DataBaseHelper.UseCache == CacheFlag.CacheAll)
            {
                var model = SqlCache.GetSqlByCode(sysDatabaseName, tableCode);
                if (model != null && model.CodeType == 2)
                {
                    tableCode = model.SqlStr;
                    isViewCode = true;
                }
            }

            string colStr = colums == null || colums.Length == 0 ? "*" : string.Join(",", colums);
            sqlStr.AppendFormat(" select {0} from {1} ", colStr, isViewCode ? string.Format("({0}) as ResultTemp", tableCode) : tableCode);

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                if (!whereStr.Trim().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                    sqlStr.Append(" WHERE ");
                sqlStr.AppendFormat(" {0}", whereStr);
            }

            if (!string.IsNullOrWhiteSpace(orderByStr))
            {
                if (!orderByStr.Trim().StartsWith("ORDER BY", StringComparison.CurrentCultureIgnoreCase))
                    orderByStr = string.Concat(" ORDER BY ", orderByStr);
            }

            int startIndex = pageIndex * pageSize + 1;
            int endIndex = (pageIndex + 1) * pageSize;

            return string.Format(" select {0} from ({1}) as ResultView where RowIndex >= {2} AND RowIndex <= {3} ", colStr, string.Format("select ROW_NUMBER() over({0}) as RowIndex ,* from ({1}) as ResultTempR ", orderByStr, sqlStr.ToString()), startIndex, endIndex);
        }

        #endregion

        #region 查询Datatable数据量

        /// <summary>
        /// 查询Datatable数据量
        /// </summary>
        /// <param name="tableCode"></param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public string SelectDatatableCount(string tableCode, string sysDatabaseName = null, string whereStr = null)
        {
            if (string.IsNullOrWhiteSpace(tableCode))
                throw new Exception("表名或视图名称不存在");

            StringBuilder sqlStr = new StringBuilder();
            bool isViewCode = false;
            if (C5DataBaseHelper.UseCache == CacheFlag.CacheView || C5DataBaseHelper.UseCache == CacheFlag.CacheAll)
            {
                var model = SqlCache.GetSqlByCode(sysDatabaseName, tableCode);
                if (model != null && model.CodeType == 2)
                {
                    tableCode = model.SqlStr;
                    isViewCode = true;
                }
            }

            sqlStr.AppendFormat(" select count(1) from {0} ", isViewCode ? string.Format("({0}) as ResultView", tableCode) : tableCode);
            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                if (!whereStr.Trim().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                    sqlStr.Append(" WHERE ");
                sqlStr.AppendFormat(" {0}", whereStr);
            }

            return sqlStr.ToString();
        }

        #endregion

        #region 获取SQL参数
        /// <summary>
        /// 获取SQL参数
        /// </summary>
        /// <param name="c5SqlModel"></param>
        /// <param name="parameterName"></param>
        /// <param name="val"></param>
        /// <returns></returns>
        private SqlParameter GetSqlParameter(C5SqlModelAttribute c5SqlModel, string parameterName, object val)
        {
            SqlParameter sqlPara = new SqlParameter() { Value = val == null ? DBNull.Value : val, ParameterName = string.Format("@{0}", parameterName) };
            switch (c5SqlModel.DbType)
            {
                case "bigint":
                    sqlPara.SqlDbType = SqlDbType.BigInt;
                    break;
                case "binary":
                    sqlPara.SqlDbType = SqlDbType.Binary;
                    sqlPara.Size = c5SqlModel.MaxLength;
                    break;
                case "bit":
                    sqlPara.SqlDbType = SqlDbType.Bit;
                    break;
                case "char":
                    sqlPara.SqlDbType = SqlDbType.Char;
                    sqlPara.Size = c5SqlModel.MaxLength;
                    break;
                case "date":
                    sqlPara.SqlDbType = SqlDbType.Date;
                    break;
                case "datetime":
                    sqlPara.SqlDbType = SqlDbType.DateTime;
                    break;
                case "datetime2":
                    sqlPara.SqlDbType = SqlDbType.DateTime2;
                    sqlPara.Size = c5SqlModel.MaxLength;
                    break;
                case "datetimeoffset":
                    sqlPara.SqlDbType = SqlDbType.DateTimeOffset;
                    sqlPara.Size = c5SqlModel.MaxLength;
                    break;
                case "decimal":
                    sqlPara.SqlDbType = SqlDbType.Decimal;
                    sqlPara.Precision = c5SqlModel.Precision;
                    sqlPara.Scale = c5SqlModel.Scale;
                    break;
                case "float":
                    sqlPara.SqlDbType = SqlDbType.Float;
                    break;
                case "image":
                    sqlPara.SqlDbType = SqlDbType.Image;
                    break;
                case "int":
                    sqlPara.SqlDbType = SqlDbType.Int;
                    break;
                case "money":
                    sqlPara.SqlDbType = SqlDbType.Money;
                    break;
                case "nchar":
                    sqlPara.SqlDbType = SqlDbType.NChar;
                    sqlPara.Size = c5SqlModel.MaxLength;
                    break;
                case "ntext":
                    sqlPara.SqlDbType = SqlDbType.NText;
                    break;
                case "numeric":
                    sqlPara.SqlDbType = SqlDbType.Decimal;
                    sqlPara.Precision = c5SqlModel.Precision;
                    sqlPara.Scale = c5SqlModel.Scale;
                    break;
                case "nvarchar":
                    sqlPara.SqlDbType = SqlDbType.NVarChar;
                    sqlPara.Size = c5SqlModel.MaxLength;
                    break;
                case "real":
                    sqlPara.SqlDbType = SqlDbType.Real;
                    break;
                case "smalldatetime":
                    sqlPara.SqlDbType = SqlDbType.SmallDateTime;
                    break;
                case "smallint":
                    sqlPara.SqlDbType = SqlDbType.SmallInt;
                    break;
                case "smallmoney":
                    sqlPara.SqlDbType = SqlDbType.SmallMoney;
                    break;
                case "text":
                    sqlPara.SqlDbType = SqlDbType.Text;
                    break;
                case "time":
                    sqlPara.SqlDbType = SqlDbType.Time;
                    sqlPara.Size = c5SqlModel.MaxLength;
                    break;
                case "timestamp":
                    sqlPara.SqlDbType = SqlDbType.Timestamp;
                    break;
                case "tinyint":
                    sqlPara.SqlDbType = SqlDbType.TinyInt;
                    break;
                case "uniqueidentifier":
                    sqlPara.SqlDbType = SqlDbType.UniqueIdentifier;
                    break;
                case "varbinary":
                    sqlPara.SqlDbType = SqlDbType.VarBinary;
                    sqlPara.Size = c5SqlModel.MaxLength;
                    break;
                case "varchar":
                    sqlPara.SqlDbType = SqlDbType.VarChar;
                    sqlPara.Size = c5SqlModel.MaxLength;
                    break;
                case "xml":
                    sqlPara.SqlDbType = SqlDbType.Xml;
                    sqlPara.Size = c5SqlModel.MaxLength;
                    break;
            }

            return sqlPara;
        }
        #endregion

        #region 查询组合视图数据集
        /// <summary>
        /// 查询组合视图数据集
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public string GetViewListSql<T>(string whereStr = null, string orderByStr = null)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            Dictionary<int, Type> typeList = GetAllClass<T>();

            foreach (var item in typeList)
            {
                if (item.Value.BaseType == null || item.Value.BaseType == typeof(object))
                {
                    //基类
                    CreateTableSql(sqlBuilder, item.Value);
                }
                else
                {
                    //派生类
                    CreateDerivedSql(sqlBuilder, item);
                }
            }

            sqlBuilder.Insert(0, " SELECT * FROM ( \n ");

            sqlBuilder.AppendFormat("\n ) AS TempView \n");

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                if (!whereStr.Trim().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                    sqlBuilder.Append(" WHERE ");
                sqlBuilder.AppendFormat(" {0} \n", whereStr);
            }

            if (!string.IsNullOrWhiteSpace(orderByStr))
            {
                if (!orderByStr.Trim().StartsWith("ORDER BY", StringComparison.CurrentCultureIgnoreCase))
                    orderByStr = string.Concat(" ORDER BY ", orderByStr);
                sqlBuilder.AppendFormat(" {0} \n", orderByStr);
            }

            return sqlBuilder.ToString();
        }
        #endregion

        #region 查询组合视图分页数据集
        /// <summary>
        /// 查询组合视图分页数据集
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public string GetViewListPagingSql<T>(int pageIndex, int pageSize, string whereStr = null, string orderByStr = null)
        {
            int startIndex = pageIndex * pageSize + 1;

            int endIndex = (pageIndex + 1) * pageSize;

            StringBuilder sqlBuilder = new StringBuilder();

            //基类已经派生类集合
            Dictionary<int, Type> typeList = GetAllClass<T>();

            //默认主键排序
            if (string.IsNullOrWhiteSpace(orderByStr))
            {
                orderByStr = GetParimaryKey(typeList);

                if (string.IsNullOrWhiteSpace(orderByStr)) throw new Exception(string.Format("视图【{0}】无主键，请检查！", typeof(T).Name));
            }

            if (!orderByStr.Trim().StartsWith("ORDER BY", StringComparison.CurrentCultureIgnoreCase))
                orderByStr = string.Concat(" ORDER BY ", orderByStr);

            int index = 0;
            bool isLastIndex = false;

            foreach (var item in typeList)
            {
                index++;
                if (index == typeList.Count) isLastIndex = true;

                if (item.Value.BaseType == null || item.Value.BaseType == typeof(object))
                {
                    //基类
                    CreateTableSql(sqlBuilder, item.Value, isLastIndex, orderByStr);
                }
                else
                {
                    //派生类
                    CreateDerivedSql(sqlBuilder, item, isLastIndex, orderByStr);
                }
            }

            sqlBuilder.Insert(0, " SELECT * FROM ( \n");

            sqlBuilder.AppendFormat(" ) AS TempView \n");

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                if (!whereStr.Trim().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                    sqlBuilder.Append(" WHERE ");
                sqlBuilder.AppendFormat(" {0}", whereStr);
                sqlBuilder.AppendFormat(" AND RowIndex >= {0} AND RowIndex <= {1} \n ", startIndex, endIndex);
            }
            else
            {
                sqlBuilder.AppendFormat(" WHERE RowIndex >= {0} AND RowIndex <= {1} \n", startIndex, endIndex);
            }

            sqlBuilder.AppendFormat(" {0} \n", orderByStr);

            return sqlBuilder.ToString();
        }
        #endregion

        #region 组合父类语句
        /// <summary>
        /// 组合父类语句
        /// </summary>
        /// <param name="sqlBuilder"></param>
        /// <param name="objType"></param>
        /// <param name="isLastIndex"></param>
        /// <param name="orderByStr"></param>
        private void CreateTableSql(StringBuilder sqlBuilder, Type objType, bool isLastIndex = false, string orderByStr = null)
        {
            sqlBuilder.Append(" SELECT ");

            if (isLastIndex == true && !string.IsNullOrWhiteSpace(orderByStr))
            {
                sqlBuilder.AppendFormat("\n ROW_NUMBER() OVER({0}) AS RowIndex,", orderByStr);
            }

            //取得类上的特性标签，false表示不获取因为继承而得到的标签
            List<C5ViewClassAttribute> viewClassList = objType.GetCustomAttributes(typeof(C5ViewClassAttribute), false).Cast<C5ViewClassAttribute>().ToList();

            //表信息
            var tableAttribute = viewClassList.FirstOrDefault(o => !string.IsNullOrWhiteSpace(o.BaseTable)) as C5ViewClassAttribute;
            if (tableAttribute == null) throw new Exception("请填写实体主表特性信息！");

            //NULL默认值
            var isnullAttribute = viewClassList.FirstOrDefault(o => !string.IsNullOrWhiteSpace(o.IsNullStrOrFun) && o.IsNullInt > 0) as C5ViewClassAttribute;

            //组合视图字段
            CreateFieldSql(sqlBuilder, objType, viewClassList, isnullAttribute, tableAttribute.BaseTable);

            sqlBuilder.AppendFormat("\n FROM {0} AS {1}", tableAttribute.BaseTable, tableAttribute.RelTag);

            //组合视图关联语句
            CreateJoinSql(sqlBuilder, viewClassList, tableAttribute.RelTag);
        }
        #endregion

        #region 组合派生类语句
        /// <summary>
        /// 组合派生类语句
        /// </summary>
        /// <param name="sqlBuilder"></param>
        /// <param name="item"></param>
        /// <param name="isLastIndex"></param>
        /// <param name="orderByStr"></param>
        private void CreateDerivedSql(StringBuilder sqlBuilder, KeyValuePair<int, Type> item, bool isLastIndex = false, string orderByStr = null)
        {
            //派生类
            StringBuilder tempBuilder = new StringBuilder();

            tempBuilder.Append(" SELECT ");

            if (isLastIndex == true && !string.IsNullOrWhiteSpace(orderByStr))
            {
                tempBuilder.AppendFormat(" ROW_NUMBER() OVER({0}) AS RowIndex,", orderByStr);
            }

            List<C5ViewClassAttribute> viewClassList = item.Value.GetCustomAttributes(typeof(C5ViewClassAttribute), false).Cast<C5ViewClassAttribute>().ToList();

            var isNullDefault = viewClassList.FirstOrDefault(o => !string.IsNullOrWhiteSpace(o.IsNullStrOrFun) || o.IsNullInt > 0) as C5ViewClassAttribute;

            //组合视图字段
            CreateFieldSql(tempBuilder, item.Value, viewClassList, isNullDefault, item.Value.Name);

            tempBuilder.AppendFormat("\n ,{0}.* \n FROM ", item.Value.Name);

            sqlBuilder.Insert(0, tempBuilder.ToString());

            sqlBuilder.AppendFormat("  {0} ", item.Value.Name);

            //组合视图关联语句
            CreateJoinSql(sqlBuilder, viewClassList, item.Value.Name);
        }
        #endregion

        #region 获取基类集合
        /// <summary>
        /// 获取基类集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        private Dictionary<int, Type> GetAllClass<T>()
        {
            Dictionary<int, Type> typeList = new Dictionary<int, Type>();

            Type thisType = typeof(T);

            int index = 1;

            typeList.Add(index, thisType);

            while (true)
            {
                index++;

                thisType = thisType.BaseType;
                if (thisType == typeof(object) || thisType == null || thisType.FullName.Contains(".BaseEntity")) break;

                typeList.Add(index, thisType);
            }

            return typeList.OrderByDescending(o => o.Key).ToDictionary(k => k.Key, v => v.Value);
        }
        #endregion

        #region 组合视图关联语句
        /// <summary>
        /// 组合视图关联语句
        /// </summary>
        /// <param name="sqlBuilder"></param>
        /// <param name="viewClassList"></param>
        /// <param name="tableTag"></param>
        private void CreateJoinSql(StringBuilder sqlBuilder, List<C5ViewClassAttribute> viewClassList, string tableTag)
        {
            //关联表类特性集合
            List<C5ViewClassAttribute> relTableList = viewClassList.Where(o => !string.IsNullOrWhiteSpace(o.RelTable)).ToList();

            foreach (var rel in relTableList)
            {
                string joinType = "LEFT";

                switch (rel.JoinType)
                {
                    case (int)JoinType.Left:
                        joinType = "LEFT";
                        break;
                    case (int)JoinType.Right:
                        joinType = "RIGHT";
                        break;
                    case (int)JoinType.Inner:
                        joinType = "INNER";
                        break;
                }

                sqlBuilder.AppendFormat("\n {0} JOIN ", joinType);

                if (!string.IsNullOrWhiteSpace(rel.RelTable) && !string.IsNullOrWhiteSpace(rel.RelFiled) && !string.IsNullOrWhiteSpace(rel.BaseFiled))
                {
                    sqlBuilder.AppendFormat(" {0} AS {1} ON {1}.{2} = {3}.{4} ", rel.RelTable, rel.RelTag, rel.RelFiled, tableTag, rel.BaseFiled);
                }

                if (!string.IsNullOrWhiteSpace(rel.JoinOther))
                {
                    sqlBuilder.AppendFormat(" {0} ", rel.JoinOther);
                }
            }
        }
        #endregion

        #region 组合视图字段
        /// <summary>
        /// 组合视图字段
        /// </summary>
        /// <param name="sqlBuilder"></param>
        /// <param name="objType"></param>
        /// <param name="viewClassList"></param>
        /// <param name="isnullAttribute"></param>
        /// <param name="thisTag"></param>
        private void CreateFieldSql(StringBuilder sqlBuilder, Type objType, List<C5ViewClassAttribute> viewClassList, C5ViewClassAttribute isnullAttribute, string thisTag)
        {
            //获取所有公有属性
            PropertyInfo[] info = objType.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.DeclaredOnly);

            foreach (PropertyInfo pro in info)
            {
                //取得属性的特性标签，false表示不获取因为继承而得到的标签
                C5ViewFieldAttribute attr = pro.GetCustomAttributes(typeof(C5ViewFieldAttribute), false).Cast<C5ViewFieldAttribute>().FirstOrDefault();

                if (attr == null && pro.GetCustomAttributes(false).Length == 0)
                {
                    sqlBuilder.AppendFormat("\n {0} AS {1},", GetIsNullDefaultSql(isnullAttribute, thisTag, attr, pro), pro.Name);
                    continue;
                }

                if (attr != null && !string.IsNullOrWhiteSpace(attr.OtherSql))
                {
                    sqlBuilder.AppendFormat("\n ({0}) AS {1} ,", attr.OtherSql, pro.Name);
                }
                else if (attr != null && !string.IsNullOrWhiteSpace(attr.WhereFiled) && !string.IsNullOrWhiteSpace(attr.CaseWhen))
                {
                    sqlBuilder.AppendFormat("\n ({0}) AS {1} ,", JoinCaseWhenSql(attr.WhereFiled, attr.CaseWhen), pro.Name);
                }
                else if (attr != null && !string.IsNullOrWhiteSpace(attr.RelTag))
                {
                    var relTagViewClass = viewClassList.FirstOrDefault(o => o.RelTag == attr.RelTag);

                    sqlBuilder.AppendFormat("\n {0} AS {1},", GetIsNullDefaultSql(isnullAttribute, relTagViewClass.RelTag, attr, pro), pro.Name);
                }
            }

            sqlBuilder.Remove(sqlBuilder.ToString().LastIndexOf(','), 1);
        }
        #endregion

        #region 获取字段SQL
        /// <summary>
        /// 获取字段SQL
        /// </summary>
        /// <param name="isnullAttribute"></param>
        /// <param name="relTag"></param>
        /// <param name="viewFieldAttribute"></param>
        /// <param name="var"></param>
        /// <returns></returns>
        private string GetIsNullDefaultSql(C5ViewClassAttribute isnullAttribute, string relTag, C5ViewFieldAttribute viewFieldAttribute, PropertyInfo var)
        {
            string sqlStr = null;

            Type type = var.GetType();

            //是否可为null
            bool isNull = IsNullableType(type);

            //字段名
            string returnField = null;

            if (viewFieldAttribute != null)
            {
                returnField = string.IsNullOrWhiteSpace(viewFieldAttribute.ReturnFiled) ? var.Name : viewFieldAttribute.ReturnFiled;
            }
            else
            {
                returnField = var.Name;
            }

            //为空赋默认值
            if (isNull)
            {
                string defaultValue = null;

                if (viewFieldAttribute.IsNull != null)
                {
                    defaultValue = viewFieldAttribute.IsNull.ToString();
                }
                else if (isnullAttribute != null)
                {
                    bool isNumber = IsNumberType(type);

                    defaultValue = isNumber ? isnullAttribute.IsNullInt.ToString() : isnullAttribute.IsNullStrOrFun;
                }

                if (!string.IsNullOrWhiteSpace(defaultValue))
                {
                    sqlStr = string.Format("ISNULL({0}.{1},{2})", relTag, returnField, defaultValue);
                    return sqlStr;
                }
            }

            sqlStr = string.Format("{0}.{1}", relTag, returnField);

            return sqlStr;
        }
        #endregion

        #region 判断是否可空类型
        /// <summary>
        /// 判断是否可空类型
        /// </summary>
        /// <param name="theType"></param>
        /// <returns></returns>
        private bool IsNullableType(Type theType)
        {
            return (theType.IsGenericType && theType.
              GetGenericTypeDefinition().Equals
              (typeof(Nullable<>)));
        }
        #endregion

        #region 判断是否数字类型
        /// <summary>
        /// 判断是否数字类型
        /// </summary>
        /// <param name="theType"></param>
        /// <returns></returns>
        private bool IsNumberType(Type theType)
        {
            if (theType == typeof(int) || theType == typeof(decimal) ||
                theType == typeof(double) || theType == typeof(long) ||
                theType == typeof(float))
            {
                return true;
            }

            return false;
        }
        #endregion

        #region 获取CASE WHEN语句
        /// <summary>
        /// 获取CASE WHEN语句
        /// </summary>
        /// <param name="whereFiled"></param>
        /// <param name="caseWhen"></param>
        /// <returns></returns>
        private string JoinCaseWhenSql(string whereFiled, string caseWhen)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendFormat(" CASE {0} ", whereFiled);

            var strList = caseWhen.Split(';');

            foreach (var str in strList)
            {

                if (str.Contains(":"))
                {
                    //条件
                    var keyStr = str.Trim().Substring(0, str.IndexOf(":"));

                    //值
                    var valueStr = str.Trim().Substring(str.IndexOf(":") + 1);

                    sqlBuilder.AppendFormat(" WHEN {0} THEN {1} ", keyStr, valueStr);
                }
                else if (str.Contains("??"))
                {
                    //ELSE信息
                    sqlBuilder.AppendFormat(" ELSE {0} ", str.Trim().Substring(2));
                }
            }

            sqlBuilder.Append(" END ");

            return sqlBuilder.ToString();
        }
        #endregion

        #region 获取主键名称
        /// <summary>
        /// 获取主键名称
        /// </summary>
        /// <param name="typeList"></param>
        /// <returns></returns>
        private string GetParimaryKey(Dictionary<int, Type> typeList)
        {
            string keyNameStr = null;

            List<string> keyNameList = new List<string>();

            foreach (var item in typeList)
            {
                PropertyInfo[] info = item.Value.GetProperties(BindingFlags.Instance | BindingFlags.Public | System.Reflection.BindingFlags.DeclaredOnly);

                var parimaryKeyProList = info.Where(o =>
                {
                    Object[] attr = o.GetCustomAttributes(typeof(C5SqlModelAttribute), false);

                    if (attr.Length == 0 || (attr[0] as C5SqlModelAttribute) == null || (attr[0] as C5SqlModelAttribute).PrimaryKey == false) return false;

                    return true;
                }).ToList();

                if (parimaryKeyProList == null || parimaryKeyProList.Count == 0) continue;

                keyNameList.AddRange(parimaryKeyProList.Select(o => o.Name).ToList());
            }

            if (keyNameList != null && keyNameList.Count > 0) keyNameStr = string.Join(",", keyNameList);

            return keyNameStr;
        }
        #endregion

        #region 获取字段信息

        /// <summary>
        /// 获取表字段信息
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="tableName"></param>
        /// <param name="prop"></param>
        /// <returns></returns>
        public C5SqlModelAttribute GetC5SqlModelAttribute(string sysDatabaseName, string tableName, PropertyInfo prop)
        {
            C5SqlModelAttribute c5SqlModel = null;
            if ((C5DataBaseHelper.UseCache == CacheFlag.CacheColumn || C5DataBaseHelper.UseCache == CacheFlag.CacheAll) && !string.IsNullOrWhiteSpace(sysDatabaseName))
            {
                c5SqlModel = SqlCache.GetColumnInfo(sysDatabaseName, tableName, prop.Name);
            }
            else
            {
                //取得属性的特性标签，false表示不获取因为继承而得到的标签
                Object[] attr = prop.GetCustomAttributes(typeof(C5SqlModelAttribute), false);
                if (attr.Length == 0)
                    return null;

                c5SqlModel = attr[0] as C5SqlModelAttribute;
            }

            return c5SqlModel;
        }

        #endregion
    }
}
